﻿using System.Collections.Generic;
using System.Data;
using Pku.BookStore.Model;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class FavoritesDAL : BaseDAL
    {
        string commandText = string.Empty;

        /// <summary>
        /// 增加收藏夹
        /// </summary>
        /// <param name="entity"></param>
        public int Add(FavoritesModel entity)
        {
            commandText = @"INSERT INTO Favorites(BookID,UserID)
VALUES(@BookID,@UserID);
SET  @FavoritesID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "FavoritesID", DbType.Int32, entity.FavoritesID);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);

            DbMain.ExecuteNonQuery(command);

            return (int)DbMain.GetParameterValue(command, "FavoritesID");

        }

        /// <summary>
        /// 修改收藏夹信息
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(FavoritesModel entity)
        {
            commandText = @"UPDATE Favorites SET BookID=@BookID,UserID=@UserID WHERE FavoritesID=@FavoritesID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "FavoritesID", DbType.Int32, entity.FavoritesID);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 搜索
        /// </summary>
        /// <returns></returns>
        public List<FavoritesModel> Search(FavoritesParam param)
        {
            List<FavoritesModel> list = new List<FavoritesModel>();
            commandText = @"SELECT 
	                            f.* ,
	                            p.BookName,
	                            p.[Image]
                            FROM 
	                            Favorites f(NOLOCK)
	                            INNER JOIN Product p(NOLOCK) ON p.BookID = f.BookID
                            WHERE 
	                            1=1
	                            AND f.UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, param.UserID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    var m = ConvertReaderToEntity(dr);
                    m.BookName = dr["BookName"].ToString();
                    m.Image = dr["Image"].ToString();
                    list.Add(m);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="favoritesID"></param>
        /// <returns></returns>
        public FavoritesModel GetEntityById(int favoritesID)
        {
            commandText = @"SELECT FavoritesID,BookID,UserID  FROM  Favorites WHERE FavoritesID=@FavoritesID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "FavoritesID", DbType.Int32, favoritesID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static FavoritesModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new FavoritesModel
            {
                FavoritesID = (int)dr["FavoritesID"],
                BookID = (int)dr["BookID"],
                UserID = (int)dr["UserID"]
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(int BookID, int UserID)
        {
            commandText = "SELECT COUNT(1) FROM Favorites f(NOLOCK) WHERE f.BookID=@BookID AND f.UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "BookID", DbType.Int32, BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, UserID);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public bool Delete(int FavoritesID)
        {
            commandText = "DELETE Favorites WHERE FavoritesID=@FavoritesID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "FavoritesID", DbType.Int32, FavoritesID);
            return (int)DbMain.ExecuteNonQuery(command) > 0;
        }
    }

}